/**
 * Copyright 2019 吉鼎科技.
 *
 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.easyplatform.dao.utils;

import cn.easyplatform.dao.DaoException;
import cn.easyplatform.dos.FieldDo;
import cn.easyplatform.dos.LogDo;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.lang.Nums;
import cn.easyplatform.type.FieldType;
import cn.easyplatform.utils.SerializationUtils;
import org.apache.commons.io.IOUtils;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.time.*;
import java.util.Date;


/**
 * @author <a href="mailto:davidchen@epclouds.com">littleDog</a> <br/>
 * @since 2.0.0 <br/>
 */
public final class SqlUtils {

    /**
     * SQL SERVER
     */
    public static final int DATETIMEOFFSET = -155;

    /**
     * 引擎内部使用
     *
     * @param rs
     * @param rsmd
     * @param index
     * @return
     */
    public static FieldDo getValue(ResultSet rs, ResultSetMetaData rsmd,
                                   int index) {
        String name = null;
        try {
            TableField tf = new TableField();
            tf.setDecimal(rsmd.getScale(index));
            tf.setLength(rsmd.getColumnDisplaySize(index));
            if (rsmd.getColumnLabel(index) != null)
                name = rsmd.getColumnLabel(index);
            else
                name = rsmd.getColumnName(index);
            // int sep = name.indexOf(".");
            // if (sep > 0)
            // name = name.substring(sep + 1);
            tf.setName(name);
            tf.setType(getType(tf.getName(), rsmd.getColumnType(index)));
            return getValue(rs, index, tf);
        } catch (Exception ex) {
            if (ex instanceof DaoException)
                throw (DaoException) ex;
            throw new DaoException("dao.access.getValue", ex, name);
        }
    }

    /**
     * 客户端使用
     *
     * @param rs
     * @param rsmd
     * @param index
     * @return
     */
    public static FieldDo getSqlValue(ResultSet rs, ResultSetMetaData rsmd,
                                      int index) {
        String name = null;
        try {
            if (rsmd.getColumnLabel(index) != null)
                name = rsmd.getColumnLabel(index);
            else
                name = rsmd.getColumnName(index);
            // int sep = name.indexOf(".");
            // if (sep > 0)
            // name = name.substring(sep + 1);
            FieldType type = getType(name, rsmd.getColumnType(index));
            FieldDo field = new FieldDo(type);
            field.setDecimal(rsmd.getScale(index));
            field.setLength(rsmd.getColumnDisplaySize(index));
            field.setName(name);
            switch (type) {
                case CHAR:
                case VARCHAR:
                case CLOB:
                    field.setValue(rs.getString(index));
                    break;
                case NUMERIC:
                    Object v = rs.getObject(index);
                    if (v instanceof BigDecimal) {
                        BigDecimal bd = (BigDecimal) v;
                        v = bd.doubleValue();
                    }
                    field.setValue(v);
                    break;
                case LONG:
                    field.setValue(rs.getLong(index));
                    break;
                case INT:
                    field.setValue(rs.getInt(index));
                    break;
                case TIME:
                    field.setValue(rs.getTime(index));
                    break;
                case DATE:
                    field.setValue(rs.getDate(index));
                    break;
                case DATETIME:
                    field.setValue(rs.getTimestamp(index));
                    break;
                case BLOB:
                    field.setValue(rs.getBytes(index));
                    break;
                case BOOLEAN:
                    field.setValue(rs.getInt(index) > 0 ? Boolean.TRUE
                            : Boolean.FALSE);
                    break;
                case OBJECT:
                    byte[] bytes = rs.getBytes(index);
                    if (bytes != null)
                        field.setValue(SerializationUtils.deserialize(bytes));
                    break;
                default:
                    throw new DaoException("dao.access.getType", name, type);
            }
            if (rs.wasNull())
                field.setValue(null);
            return field;
        } catch (Exception ex) {
            if (ex instanceof DaoException)
                throw (DaoException) ex;
            throw new DaoException("dao.access.getValue", ex, name);
        }
    }

    /**
     * @param name
     * @param sqlType
     * @return
     * @throws SQLException
     */
    public static FieldType getType(String name, int sqlType) {
        switch (sqlType) {
            case Types.BIT:
            case Types.TINYINT:
            case Types.INTEGER:
            case Types.SMALLINT:
                return FieldType.INT;
            case Types.BIGINT:
                return FieldType.LONG;
            case Types.CLOB:
            case Types.LONGNVARCHAR:
            case Types.LONGVARCHAR:
            case Types.SQLXML:
                return FieldType.CLOB;
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.NCHAR:
            case Types.NCLOB:
            case Types.NVARCHAR:
            case Types.ROWID:
                return FieldType.VARCHAR;
            case Types.BOOLEAN:
                return FieldType.BOOLEAN;
            case Types.BLOB:
            case Types.LONGVARBINARY:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.STRUCT:
                return FieldType.BLOB;
            case Types.JAVA_OBJECT:
                return FieldType.OBJECT;
            case Types.DATE:
                return FieldType.DATE;
            case Types.TIME:
                return FieldType.TIME;
            case Types.TIMESTAMP:
            case DATETIMEOFFSET:
                return FieldType.DATETIME;
            case Types.NUMERIC:
            case Types.DOUBLE:
            case Types.FLOAT:
            case Types.REAL:
            case Types.DECIMAL:
                return FieldType.NUMERIC;
        }
        throw new DaoException("dao.access.getType", name, sqlType);
    }

    /**
     * @param rs
     * @param index
     * @param tf
     * @return
     * @throws SQLException
     */
    public static FieldDo getValue(ResultSet rs, int index, TableField tf) {
        try {
            FieldDo field = new FieldDo(tf.getType());
            field.setName(tf.getName());
            field.setAcc(tf.getAcc());
            field.setDecimal(tf.getDecimal());
            field.setLength(tf.getLength());
            field.setDescription(tf.getDescription());
            switch (tf.getType()) {
                case CHAR:
                case VARCHAR:
                case CLOB:
                    field.setValue(rs.getString(index));
                    break;
                case NUMERIC:
                    Object v = rs.getObject(index);
                    if (v instanceof BigDecimal) {
                        BigDecimal bd = (BigDecimal) v;
                        v = bd.doubleValue();
                    }
                    field.setValue(v);
                    break;
                case LONG:
                    field.setValue(rs.getLong(index));
                    break;
                case INT:
                    field.setValue(rs.getInt(index));
                    break;
                case TIME:
                    field.setValue(rs.getTime(index));
                    break;
                case DATE:
                    field.setValue(rs.getDate(index));
                    break;
                case DATETIME:
                    field.setValue(rs.getTimestamp(index));
                    break;
                case BLOB:
                    field.setValue(rs.getBytes(index));
                    break;
                case BOOLEAN:
                    field.setValue(rs.getInt(index) > 0 ? Boolean.TRUE
                            : Boolean.FALSE);
                    break;
                case OBJECT:
                    byte[] bytes = rs.getBytes(index);
                    if (bytes != null)
                        field.setValue(SerializationUtils.deserialize(bytes));
                    break;
                default:
                    throw new DaoException("dao.access.getType", tf.getName(),
                            tf.getType());
            }
            if (rs.wasNull())
                field.setValue(null);
            return field;
        } catch (Exception ex) {
            throw new DaoException("dao.access.getValue", ex, tf.getName());
        }
    }

    /**
     * @param pstmt
     * @param index
     * @param fd
     */
    public static void setValue(PreparedStatement pstmt, int index, FieldDo fd) {
        try {
            switch (fd.getType()) {
                case CHAR:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.CHAR);
                    else
                        pstmt.setString(index, fd.getValue().toString());
                    break;
                case VARCHAR:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.VARCHAR);
                    else
                        pstmt.setString(index, fd.getValue().toString());
                    break;
                case CLOB:
                    setClobValue(pstmt, index, fd);
                    break;
                case LONG:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.BIGINT);
                    else if (fd.getValue() instanceof Long)
                        pstmt.setLong(index, (Long) fd.getValue());
                    else if (fd.getValue() instanceof Integer)
                        pstmt.setInt(index, (Integer) fd.getValue());
                    else if (fd.getValue() instanceof Short)
                        pstmt.setShort(index, (Short) fd.getValue());
                    else if (fd.getValue() instanceof Byte)
                        pstmt.setByte(index, (Byte) fd.getValue());
                    else if (fd.getValue() instanceof Number)
                        pstmt.setLong(index, ((Number) fd.getValue()).longValue());
                    else
                        pstmt.setLong(index,
                                Nums.toLong(fd.getValue().toString(), 0));
                    break;
                case INT:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.INTEGER);
                    else if (fd.getValue() instanceof Long)
                        pstmt.setLong(index, (Long) fd.getValue());
                    else if (fd.getValue() instanceof Integer)
                        pstmt.setInt(index, (Integer) fd.getValue());
                    else if (fd.getValue() instanceof Short)
                        pstmt.setShort(index, (Short) fd.getValue());
                    else if (fd.getValue() instanceof Byte)
                        pstmt.setByte(index, (Byte) fd.getValue());
                    else if (fd.getValue() instanceof Number)
                        pstmt.setInt(index, ((Number) fd.getValue()).intValue());
                    else if (fd.getValue() instanceof Boolean)
                        pstmt.setInt(index, ((Boolean) fd.getValue()) ? 1 : 0);
                    else
                        pstmt.setInt(index, Nums.toInt(fd.getValue().toString(), 0));
                    break;
                case NUMERIC:
                    if (fd.getValue() == null)
                        pstmt.setNull(index, Types.NUMERIC);
                    else if (fd.getValue() instanceof Float)
                        pstmt.setFloat(index, (Float) fd.getValue());
                    else if (fd.getValue() instanceof Double)
                        pstmt.setDouble(index, (Double) fd.getValue());
                    else if (fd.getValue() instanceof Boolean)
                        pstmt.setInt(index, ((Boolean) fd.getValue()) ? 1 : 0);
                    else
                        pstmt.setBigDecimal(index, new BigDecimal(fd.getValue()
                                .toString()));
                    break;
                case TIME:
                    if (fd.getValue() != null)
                        pstmt.setTime(index,
                                new Time(((Date) fd.getValue()).getTime()));
                    else
                        pstmt.setNull(index, Types.TIME);
                    break;
                case DATE:
                    setDateValue(pstmt, index, fd);
                    break;
                case DATETIME:
                    setDatetimeValue(pstmt, index, fd);
                    break;
                case BLOB:
                    setBlobValue(pstmt, index, fd);
                    break;
                case BOOLEAN:
                    if (fd.getValue() == null)
                        pstmt.setInt(index, 0);
                    else if (fd.getValue() instanceof Boolean) {
                        Boolean b = (Boolean) fd.getValue();
                        pstmt.setInt(index, b ? 1 : 0);
                    } else if (fd.getValue() instanceof Number) {
                        pstmt.setInt(index,
                                ((Number) fd.getValue()).intValue() > 0 ? 1 : 0);
                    } else {
                        String s = fd.getValue().toString();
                        pstmt.setInt(index, s.equalsIgnoreCase("true") ? 1 : 0);
                    }
                    break;
                case OBJECT:
                    if (fd.getValue() == null)
                        pstmt.setObject(index, null);
                    else
                        pstmt.setBytes(index,
                                SerializationUtils.serialize(fd.getValue()));
                    break;
                default:
                    throw new DaoException("dao.access.getType", fd.getRawName(),
                            fd.getType(), fd.getValue());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DaoException("dao.access.setValue", ex, fd.getRawName());
        }
    }


    /**
     * jdk8时间转成通用的date对象
     *
     * @param val
     * @return
     */
    public static Object convert(Object val) throws SQLException {
        if (val instanceof LocalDateTime) {
            ZonedDateTime zdt = ((LocalDateTime) val).atZone(ZoneId.systemDefault());
            val = Date.from(zdt.toInstant());
        } else if (val instanceof LocalDate) {
            ZonedDateTime zdt = ((LocalDate) val).atStartOfDay(ZoneId.systemDefault());
            val = Date.from(zdt.toInstant());
        } else if (val instanceof LocalTime) {
            LocalTime localTime = LocalTime.now();
            val = Timestamp.valueOf(localTime.atDate(LocalDate.now()));
        } else if (val instanceof ZonedDateTime) {
            val = Date.from(((ZonedDateTime) val).toInstant());
        } else {
            try {
                if (val instanceof Clob)
                    val = IOUtils.toString(((Clob) val).getAsciiStream(), "utf-8");
                else if (val instanceof Blob)
                    val = IOUtils.toByteArray(((Blob) val).getBinaryStream());
            } catch (IOException e) {
                throw new SQLException(e);
            }
        }
        return val;
    }

    // /////////////以下的函数在不同的数据库类型可能处理的方式不一样////////////////////////

    /**
     * @param pstmt
     * @param index
     * @param fd
     * @throws SQLException
     */
    protected static void setClobValue(PreparedStatement pstmt, int index,
                                       FieldDo fd) throws SQLException {
        if (fd.getValue() == null)
            pstmt.setNull(index, Types.CLOB);
        else
            pstmt.setString(index, (String) fd.getValue());
    }

    /**
     * @param pstmt
     * @param index
     * @param fd
     * @throws SQLException
     */
    protected static void setBlobValue(PreparedStatement pstmt, int index,
                                       FieldDo fd) throws SQLException {
        if (fd.getValue() == null)
            pstmt.setNull(index, Types.BLOB);
        else {
            byte[] data = null;
            if (fd.getValue() instanceof byte[])
                data = (byte[]) fd.getValue();
            else
                data = SerializationUtils.serialize(fd.getValue());
            pstmt.setBytes(index, data);
        }
    }

    /**
     * @param pstmt
     * @param index
     * @param fd
     * @throws SQLException
     */
    protected static void setDateValue(PreparedStatement pstmt, int index,
                                       FieldDo fd) throws SQLException {
        if (fd.getValue() != null)
            pstmt.setDate(index,
                    new java.sql.Date(((Date) fd.getValue()).getTime()));
        else
            pstmt.setNull(index, Types.DATE);
    }

    /**
     * @param pstmt
     * @param index
     * @param fd
     * @throws SQLException
     */
    protected static void setDatetimeValue(PreparedStatement pstmt, int index,
                                           FieldDo fd) throws SQLException {
        if (fd.getValue() != null)
            pstmt.setTimestamp(index,
                    new Timestamp(((Date) fd.getValue()).getTime()));
        else
            pstmt.setNull(index, Types.TIMESTAMP);
    }

    /**
     * 设置参数
     *
     * @param pstmt
     * @param parameter
     * @throws SQLException
     */
    public static void setParameter(PreparedStatement pstmt, Object... parameter) throws SQLException {
        ParameterMetaData pmd = pstmt.getParameterMetaData();
        if (pmd.getParameterCount() != parameter.length)
            throw new SQLException("The number of parameters are not equal");
        for (int i = 0; i < parameter.length; i++) {
            //if (parameter[i] != null) {
            pstmt.setObject(i + 1, parameter[i]);
            //} else
            //     pstmt.setNull(i + 1, pmd.getParameterType(i + 1));
        }
    }

    /**
     * 写日志
     *
     * @param conn
     * @param ld
     */
    public static void log(Connection conn, LogDo ld) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(
                    "insert into sys_event_info(id,userId,eventTime,deviceType,type,event,content) values(?,?,?,?,?,?,?)");
            pstmt.setLong(1, ld.getId());
            pstmt.setString(2, ld.getUserId());
            pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            pstmt.setString(4, ld.getDeviceType());
            pstmt.setInt(5, ld.getType());
            pstmt.setString(6, ld.getEvent());
            pstmt.setString(7, ld.getContent());
            pstmt.execute();
        } catch (SQLException ex) {
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }
}
